﻿#region 代码引用

using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using Aspose.Cells;

#endregion

namespace Utils.Aspose
{
    public class AsposeExcel
    {
        private Workbook workbook;
        private Worksheet worksheet;
        private Cells cells;
        public List<string> SheetNames;

        public AsposeExcel()
        {
            workbook = new Workbook();
            worksheet = workbook.Worksheets[0];
            cells = worksheet.Cells;
        }

        public AsposeExcel(string xlsPath)
        {
            workbook = new Workbook(xlsPath);
            worksheet = workbook.Worksheets[0];
            cells = worksheet.Cells;
            GetSheetsName();
        }

        public List<string> GetSheetsName() => SheetNames = workbook.Worksheets.Select(sheetName => sheetName.Name).ToList();

        public DataTable ReadToDataTable(string sheetName, bool isHeader)
        {
            worksheet = workbook.Worksheets[sheetName];
            cells = worksheet.Cells;
            DataTable dt = InitDataTable(isHeader, new[,] { { 0, 0 } }, new[,] { { 0, cells.MaxColumn } });

            List<object> dtNewRow = new List<object>();
            foreach (Row row in cells.Rows)
            {
                if (isHeader && row.Index.Equals(0)) continue;
                dtNewRow.Clear();
                dtNewRow.AddRange(cells.Columns.Select(col => GetCellValueToString(row[col.Index])));
                dt.Rows.Add(dtNewRow.ToArray());
            }
            dt.TableName = sheetName;
            return dt;
        }

        public DataSet ReadToDataSet(string[] sheetName, bool isHeader)
        {
            DataSet ds = new DataSet();
            foreach (string sn in sheetName)
            {
                ds.Tables.Add(ReadToDataTable(sn, isHeader).Copy());
            }
            return ds;
        }

        public void SetWorkSheet(string sheetName)
        {
            worksheet = workbook.Worksheets[sheetName];
            cells = worksheet.Cells;
        }

        public void WriteValue(int rowIndex, int colIndex, string value)
        {
            cells[rowIndex, colIndex].PutValue(value);
            workbook.CalculateFormula();
        }

        public void WriteValue(DataTable dt, bool whetherWrittenHeader, int rowIndex, int colIndex, bool insertRows)
        {
            cells.ImportDataTable(dt, whetherWrittenHeader, rowIndex, colIndex, insertRows);
            workbook.CalculateFormula();
        }

        public void SaveToExcel(string path)
        {
            string pathSufix = Path.GetExtension(path);
            if (string.IsNullOrWhiteSpace(pathSufix)) throw new Exception("保存文件后缀错误,现只支持*.doc,*.docx后缀");
            string dir = path.Replace(Path.GetFileName(path), "");
            if (!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }
            SaveFormat sufix = pathSufix.Equals(".Xlsx", StringComparison.OrdinalIgnoreCase)
                ? SaveFormat.Xlsx
                : SaveFormat.Excel97To2003;
            workbook.Save(path, sufix);
        }

        private DataTable InitDataTable(bool isHeader, int[,] startIndex, int[,] endIndex)
        {
            DataTable dt = new DataTable();
            if (isHeader)
            {
                int rowIndex = startIndex[0, 0];
                for (int i = 0; i <= endIndex[0, 1]; i++)
                {
                    dt.Columns.Add(GetMaxColName(dt, GetCellValueToString(cells[rowIndex, i])));
                }
                return dt;
            }

            for (int i = 1; i <= endIndex[0, 1]; i++)
            {
                dt.Columns.Add("F" + i);
            }
            return dt;
        }

        private string GetMaxColName(DataTable dt, string strColName)
        {
            string strNewColName = strColName;
            int i = 1;
            while (dt.Columns.Contains(strNewColName))
            {
                strNewColName = strColName + "_" + i;
                i++;
            }
            return strNewColName;
        }

        private string GetCellValueToString(Cell cell)
        {
            string type = cell.Type.ToString();
            string strValue;
            switch (type)
            {
                case "IsDateTime":
                    DateTime time = cell.DateTimeValue;
                    strValue = time.Hour > 0
                        ? cell.DateTimeValue.ToString("yyyy-MM-dd hh:mm:ss")
                        : time.ToString("yyyy-MM-dd 00:00:00");
                    break;
                case "IsNumeric":
                    strValue = cell.DoubleValue.ToString(CultureInfo.InvariantCulture);
                    break;
                default:
                    strValue = cell.StringValue;
                    break;
            }
            return strValue;
        }
    }
}